﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;

namespace IntelligentCMS
{
    public class CMSDataLayerMSSQL
    {


        public static void CreateNewUser(string Username, string Password, string Email, string SecurityQuestion, string SecurityAnswer, string Homepage)
        {
            CMSDbDataLayer login = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);
            // Todo -> Hash and salt the password with the chosen Security Layer.
            login.AddQuery("INSERT INTO [User] ([Username], [Password], [Email], [SecurityQuestion], [SecurityAnswer], [DateTime], [Homepage]) VALUES ('" + common.GetBase64String(Username) + "', '" + common.GetBase64String(Password) + "', '" + common.GetBase64String(Email) + "', '" + common.GetBase64String(SecurityQuestion) + "', '" + common.GetBase64String(SecurityAnswer) + "', GETDATE(), '" + common.GetBase64String(Homepage) + "')");
            login.SelectQuery = false;
            login.Execute();

            DataTable d = GetHomepageUserIDFromUsername(Username);

            long userid = (long)d.Rows[0][1];

            CMSDbDataLayer login2 = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);
            // Todo -> Hash and salt the password with the chosen Security Layer.
            login2.AddQuery("INSERT INTO [Profile] ([UserID], [DateTime]) VALUES ('" + userid + "', '" + DateTime.Now + "')");
            login2.SelectQuery = false;
            login2.Execute();

        }

        public static List<string> GetAllTags()
        {
            List<string> tags = new List<string>();

            CMSDbDataLayer contenttypetags = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);

            contenttypetags.AddQuery("SELECT [Tag] FROM [ContentType]");
            contenttypetags.SelectQuery = true;
            contenttypetags.Execute();

            foreach (DataRow row in contenttypetags.Data.Rows)
            {
                tags.Add(common.FromBase64String(row["Tag"].ToString()));

            }


            CMSDbDataLayer fieldtags = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);

            fieldtags.AddQuery("SELECT [Tag] FROM [Field]");
            fieldtags.SelectQuery = true;
            fieldtags.Execute();


            foreach (DataRow row in fieldtags.Data.Rows)
            {
                tags.Add(common.FromBase64String(row["Tag"].ToString()));

            }

            return tags;

        }



        public static DataTable GetHomepageUserIDFromUsername(string Username)
        {


            CMSDbDataLayer login = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);

            login.AddQuery("SELECT [Homepage], [ID] FROM [User] WHERE [Username] = '" + common.GetBase64String(Username) + "'");
            login.SelectQuery = true;
            login.Execute();

            return login.Data;

        }

        public static DataTable GetUsernameFromUserID(long userid)
        {


            CMSDbDataLayer login = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);

            login.AddQuery("SELECT [Username] FROM [User] WHERE [ID] = '" + userid + "'");
            login.SelectQuery = true;
            login.Execute();

            return login.Data;

        }

        public static DataTable GetHomepageUserIDFromUsernamePassword(string Username, string Password)
        {

            CMSDbDataLayer login = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);
            // It puts the lotion on its skin, or else it gets the hose again. -> Silence of the Lambs.
            // It escapes the input with the string, or else it gets the sql-injection attack again! -> Next Tuesday, if you don't sanitize the input.
            login.AddQuery("SELECT [Homepage], [ID] FROM [User] WHERE [Username] = '" + common.GetBase64String(Username) + "' AND [Password] = '" + common.GetBase64String(Password) + "'");
            login.SelectQuery = true;
            login.Execute();

            return login.Data;


        }

        public static DataTable GetFilenameFromField(string type)
        {

            CMSDbDataLayer filename = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);
            // It puts the lotion on its skin, or else it gets the hose again. -> Silence of the Lambs.
            // It escapes the input with the string, or else it gets the sql-injection attack again! -> Next Tuesday, if you don't sanitize the input.
            filename.AddQuery("SELECT File.[Filename] FROM File INNER JOIN Plugin ON Plugin.[PluginFileID] = File.[ID] INNER JOIN FieldType ON FieldType.[PluginID] = Plugin.[ID] WHERE FieldType.[Type] = '" + type + "'");
            filename.SelectQuery = true;
            filename.Execute();

            return filename.Data;

        }


        public static DataTable GetAllFields()
        {

            CMSDbDataLayer fieldtypes = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);
            // It puts the lotion on its skin, or else it gets the hose again. -> Silence of the Lambs.
            // It escapes the input with the string, or else it gets the sql-injection attack again! -> Next Tuesday, if you don't sanitize the input.
            fieldtypes.AddQuery("SELECT Field.[Tag] FROM Field");
            fieldtypes.SelectQuery = true;
            fieldtypes.Execute();

            return fieldtypes.Data;

        }

        public static void UpdateProfile(long userid, string firstname, string lastname, string address, string city, string state, string zip, string country, string cellphone, string workphone, string homephone)
        {
            CMSDbDataLayer login = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);
            // Todo -> Hash and salt the password with the chosen Security Layer.
            login.AddQuery("UPDATE [Profile] SET FirstName = '" + common.GetBase64String(firstname) + "', LastName = '" + common.GetBase64String(lastname) + "', Address = '" + common.GetBase64String(address) + "', City = '" + common.GetBase64String(city) + "', State = '" + common.GetBase64String(state) + "', Zip = '" + common.GetBase64String(zip) + "', Country = '" + common.GetBase64String(country) + "', CellPhone = '" + common.GetBase64String(cellphone) + "', WorkPhone = '" + common.GetBase64String(workphone) + "', HomePhone = '" + common.GetBase64String(homephone) + "', DateTime = '" + DateTime.Now + "' WHERE UserID = '" + userid + "'");
            login.SelectQuery = false;
            login.Execute();


        }

        public static DataTable GetProfile(long userid)
        {

            CMSDbDataLayer login = new CMSDbDataLayer(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString, CMSDbDataHelper.Microsoft);
            // It puts the lotion on its skin, or else it gets the hose again. -> Silence of the Lambs.
            // It escapes the input with the string, or else it gets the sql-injection attack again! -> Next Tuesday, if you don't sanitize the input.
            login.AddQuery("SELECT [FirstName], [LastName], [Address], [City], [State], [Zip], [Country], [CellPhone], [WorkPhone], [HomePhone] FROM [Profile] WHERE [UserID] = '" + userid + "'");
            login.SelectQuery = true;
            login.Execute();

            return login.Data;


        }
    }
}
